//
//  dataBaseHandle.m
//  Cup
//
//  Created by dllo on 15/4/29.
//  Copyright (c) 2015年 doll. All rights reserved.
//

#import "dataBaseHandle.h"

@implementation dataBaseHandle
//单例方法
+(instancetype)shareDB
{
    static dataBaseHandle *dataBase = nil;
    static dispatch_once_t ontToTake;
    dispatch_once(&ontToTake, ^{
        dataBase = [[dataBaseHandle alloc] init];
    });
    return dataBase;
}

- (void)openDB
{
    //获取沙盒路径
    NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sandBoxPath = [sandBox lastObject];
    //拼接数据库路径
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"saveCup.sqlite"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"开启数据库成功");
        NSLog(@"%@",dbPath);
    }else{
        NSLog(@"开启数据库失败");
    }
}
//创建歌曲表
- (void)creatPlayerTable
{
    //sql语句
    NSString *sqlStr = @"create table if not exists savePlayer(title text , imgUrl text, musicid text,number integer primary key autoincrement)";
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建歌曲表成功");
    }else{
        NSLog(@"创建歌曲表失败");
    }
}
//插入歌曲
-(void)insertPlayerWithTitle:(NSString *)title imgUrl:(NSString *)imgUrl musicid:(NSString *)musicid
{
    NSString *insertPlayer = [NSString stringWithFormat:@"insert into savePlayer(title,imgUrl,musicid) values('%@','%@','%@')",title,imgUrl,musicid];
    int result = sqlite3_exec(dbPoint, [insertPlayer UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//查询歌曲
- (NSMutableArray *)selectPlayer
{
    NSString *selectPlayer = [NSString stringWithFormat:@"select * from savePlayer"];
    //需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    //第三个参数,查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectPlayer UTF8String], -1, &stmt, nil);
    //这个方法相当于数据库,sql跟随指针关联在一起,一同完成查询的操作
    //初始化装歌曲的大数组
    NSMutableArray *PlayerArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //放跟随指针以行进行查询,遍历,如果没有行,则停止循环
            //满足循环条件,则逐列取出内容
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *imgUrl = sqlite3_column_text(stmt, 1);
            const unsigned char *musicid = sqlite3_column_text(stmt, 2);
            //把取出来的内容进行类型转换
            NSString *titleplay = [NSString stringWithUTF8String:(const char *)title];
            NSString *imgUrlplay = [NSString stringWithUTF8String:(const char *)imgUrl];
            NSString *musicidplay = [NSString stringWithUTF8String:(const char *)musicid];
            //给对象进行赋值
            PlayInfo *player = [[PlayInfo alloc] init];
            player.title = titleplay;
            player.imgUrl = imgUrlplay;
            player.musicid = musicidplay;
            [PlayerArr addObject:player];
            [player release];
        }
    }else{
        NSLog(@"查询失败");
    }
    return PlayerArr;
}

//创建文章表
- (void)creatReadTable
{
    
    NSString *sqlStr = @"create table if not exists saveRead(title text,picUrl text,id text,number integer primary key autoincrement)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建文章表成功");
    }else{
        NSLog(@"创建文章表失败");
    }
}

//插入文章
- (void)insertReadWithTitle:(NSString *)title picUrl:(NSString *)picUrl id:(NSString *)id
{
    NSString *insertRead = [NSString stringWithFormat:@"insert into saveRead(title,picUrl,id) values('%@','%@','%@')",title,picUrl,id];
    int result = sqlite3_exec(dbPoint, [insertRead UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//查询文章
- (NSMutableArray *)selectRead
{
    NSString *selectRead = @"select *from saveRead";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, [selectRead UTF8String], -1, &stmt, nil);
    NSMutableArray *ReadArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 0);
            const unsigned char *picUrl = sqlite3_column_text(stmt,1);
            const unsigned char *id = sqlite3_column_text(stmt,2);
            //把取出来的内容进行类型转换
            NSString *titleread = [NSString stringWithUTF8String:(const char *)title];
            NSString *picUrlread = [NSString stringWithUTF8String:(const char *)picUrl];
            NSString *conidread = [NSString stringWithUTF8String:(const char *)id];
            //给对象进行赋值
            Read *readMo = [[Read alloc] init];
            readMo.title = titleread;
            readMo.picUrl = picUrlread;
            readMo.id = conidread;
            [ReadArr addObject:readMo];
            [readMo release];
        }
    }else{
        NSLog(@"查询失败");
    }
    return ReadArr;
}
//删除歌曲
- (void)deletePlayerWithTitle:(NSString *)title
{
    NSString *deletePlay = [NSString stringWithFormat:@"delete from savePlayer where title = '%@'",title];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deletePlay UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除文章
- (void)deleteReadWithTitle:(NSString *)title{
    NSString *deleteRead= [NSString stringWithFormat:@"delete from saveRead where title = '%@'",title];
    //执行语句
    int result = sqlite3_exec(dbPoint, [deleteRead UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
//删除歌曲表
- (void)dropPlayerTable{
    NSString *dropPlayer = [NSString stringWithFormat:@"drop table savePlayer"];
    int result = sqlite3_exec(dbPoint, [dropPlayer UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除歌曲表成功");
    }else{
        NSLog(@"删除歌曲表失败");
    }
}
//删除文章表
- (void)dropReadTable
{
    NSString *dropRead = [NSString stringWithFormat:@"drop table saveRead"];
    int result = sqlite3_exec(dbPoint, [dropRead UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除歌曲表成功");
    }else{
        NSLog(@"删除歌曲表失败");
    }
}
//关闭数据库
- (void)closeDB
{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }else{
        NSLog(@"关闭数据失败");
    }
}

- (void)night
{
    [self.delegate changeNight];
}
- (void)day
{
    [self.delegate changeDay];
}



@end
